"""
Created on May 10, 2010
@author: Ionel Anton <ionelanton.ca@gmail.com>

A sort of ORM for Web2py build above the DAL
It creates DTOs from tables structure.
"""

class DataType:
    """ Web2py DAL data types """
    id = 'id'
    integer = 'integer'
    float = 'float'
    boolean = 'boolean'
    string = 'string'
    text = 'text'
    double = 'double'
    decimal = 'decimal'
    date = 'date'
    time = 'time'
    password = 'password'
    
    """ null type """
    null = None
    
    """ ORM types """
    Attribute = '<Attribute>'
    DTO = '<DTO>'
    DAO = '<DAO>'

class Operator:
    """ SQL operators """
    EQUALS = 0
    NOT_EQUAL = 1
    GREATER_THAN = 2
    LESS_THAN = 3
    STARTS_WITH = 4
    ENDS_WITH = 5
    CONTAINS = 6

class Attribute(object):
    """ An attribute """

    def __init__(self, name, type, value = None, label = ''):
        """
        @param name:
        @param type:
        @param value:
        @param label:  
        """
        
        """
        @attention: the arguments are not inside the body of the function, so,
        once initialized, each call came with the first time initialized values
        """
        if value == None: value = DataType.null
        if label == '': label = ''
             
        """ Members """
        self.name = name
        self.type = type
        self.value = value
        if label == '':
            self.label = name
        else:
            self.label = label
            
    def __repr__(self):
        return DataType.Attribute
    
    def set(self, attr):
        self.name = attr.name
        self.type = attr.type
        self.value = attr.value
        self.label = attr.label

    def copy(self):
        return Attribute(self.name, self.type, self.value, self.label)
    
    def isSimilarTo(self, attr):
        return self.name == attr.name and self.type == attr.type
                
            
class DTO(object):
    """ The DTO contains a list of attributes """
    
    def __init__(self):
        """ Members """
        self.attributes = []
        
    def __repr__(self):
        return DataType.DTO

    def __getattr__(self, name):
        for attr in self.attributes:
            if attr.name == name:
                return attr
        return None
        
#    def __setattr__(self, name, value):
#        pass
        
    def __getitem__(self, name):
        for attr in self.attributes:
            if attr.name == name:
                return attr
        return None

    def __setitem__(self, name, value):
        for attr in self.attributes:
            if attr.name == name:
                attr.value = value
                break
            
    def __delitem__(self, name):
        for attr in self.attributes:
            if attr.name == name:
                self.attributes.remove(attr)
                break

    def setAttr(self, attr):
        for a in self.attributes:
            if a.name == attr.name:
                a.set(attr)
                break
    
    def getAttr(self, name):
        for attr in self.attributes:
            if attr.name == name:
                return attr
        return None
            
    def addAttr(self, attr, index = -1):
        if index == -1: index = -1
        if index >= 0 and index <= len(self.attributes):
            self.attributes.insert(index, attr)
        else:
            self.attributes.append(attr)        

    def copy(self):
        dto = DTO()
        for attr in self.attributes:
            dto.addAttr(attr.copy())
        return dto

    def empty(self):
        """ Sets all attributes values to None """
        for attr in self.attributes:
            attr.value = None
            
    def isSimilarTo(self, dto):
        for attr in self.attributes:
            if not attr.isSimilarTo(dto.getAttr(attr.name)):
                return False
        return True

    def inflate(self):
        """
        Join all attributes of nested DTOs with current attributes.
        """
        dto = DTO()
        dto.attributes = self.__inflate()
        return dto

    def __inflate(self):
        """
        A DTO can hold inside his attributes other DTOs.
        We join all attributes of nested DTOs with current attributes.
        """
        attrs = []
        for attr in self.attributes:
            if attr.type == DataType.DTO:
                attrs.extend(attr.value.__inflate())
            else:
                attrs.append(attr.copy())
        return attrs

class DAO(object):
    """
    Generate a complete ready to use DTO
    """
    
    def __init__(self, db, table_name, field_names = []):
        """
        @param db: gluon.sql.DAL
        @param table_name: name of the table
        @param field_names: list of field names used to initialize the DTO member
        """
        
        """ Argument verification """
        if field_names == [] : field_names = []
        
        """ Members """
        self.db = db
        self.table_name = table_name
        self.field_names = field_names
        self.dto = DTO()
        
        if self.field_names == [] : self.field_names = self.db[self.table_name].fields
        
        for field_name in self.field_names:
            if field_name in self.db[self.table_name].fields:
                self.dto.addAttr(Attribute(field_name,
                                           self.db[self.table_name][field_name].type,
                                           None,
                                           self.db[self.table_name][field_name].label))
            else:
                raise Exception('There is no field "' + field_name + '" in the table_name "' + self.table_name + '"')
        
    def __repr__(self):
        return DataType.DAO
    
    def getRows(self, op):
        query = self.db[self.table_name]['id'] > 0
        for field_name in self.field_names:
            if not self.dto[field_name].value is None:
                query &= self.__build_query(self.db[self.table_name][field_name], op, self.dto[field_name].value)
        print "query: " + str(query) + '\n'
        return self.db(query).select()

    def persist(self, dto):
        """
        SQL INSERT EQUIVALENT
        @return: id of the inserted record
        """
        if self.dto.isSimilarTo(dto):
            sql_args = dict()
            for attr in dto.attributes:
                if 'id' != attr.name:
                    sql_args[attr.name] = dto[attr.name].value
            id = self.db[self.table_name].insert(**sql_args)
            self.db.commit()
            return id
        else:
            raise Exception("Incompatible dto object") 
        
    def update(self, dto):
        """ SQL UPDATE EQUIVALENT """
        if self.dto.isSimilarTo(dto):
            sql_args = dict()
            for attr in dto.attributes:
                if 'id' != attr.name:
                    sql_args[attr.name] = dto[attr.name].value
            if not dto.id.value is None: 
                self.db(self.db[self.table_name].id == dto.id.value).update(**sql_args)
                self.db.commit()
            else:
                raise Exception("No id -> Cannot update!")
        else:
            raise Exception("Incompatible dto object")
    
    def removeById(self, id):
        """ SQL DELETE BY ID EQUIVALENT """
        self.db(self.db[self.table_name].id == id).delete()
        self.db.commit()
    
    def removeByExample(self, dto):
        dto_list = self.findByExample(dto)
        for each_dto in dto_list:
            self.removeById(each_dto.id.value)
    
    def removeLike(self, dto):
        dto_list = self.findLike(dto)
        for each_dto in dto_list:
            self.removeById(each_dto.id.value)
    
    def removeByComparison(self, op, dto):
        dto_list = self.findByComparison(dto)
        for each_dto in dto_list:
            self.removeById(each_dto.id.value)

    def findById(self, id):
        """
        SQL SELECT BY ID EQUIVALENT
        @note: DAL assumes 'id' as primary key
        @param id: id of the record in database
        """
        dto_copy = self.dto.copy()
        rows = self.db(self.db[self.table_name]['id'] == id).select()
        if rows:
            for row in rows:
                for field_name in self.field_names:
                    dto_copy.setAttr(Attribute(field_name,
                                               self.db[self.table_name][field_name].type,
                                               row[field_name],
                                               self.db[self.table_name][field_name].label))
                break ## only first row is needed
            return dto_copy
        else:
            return None    

    def findByIdRecursive(self, id):
        """ Select all records in the related tables """
        dto_copy = self.dto.copy()
        rows = self.db(self.db[self.table_name]['id'] == id).select()
        if rows:
            for row in rows:
                for field_name in self.field_names:
                    if self.__isForeignKey(field_name):
                        dao = DAO(self.db,
                                  self.__returnParentTable(field_name),
                                  self.__returnListOfReferencedFields(field_name, False))
                        dto_copy.setAttr(Attribute(field_name,
                                                   DataType.DTO,
                                                   dao.findByIdRecursive(row[field_name]), ## id of record in parent table
                                                   self.__returnPrimaryKey(field_name)))
                    else:
                        dto_copy.setAttr(Attribute(field_name,
                                                   self.db[self.table_name][field_name].type,
                                                   row[field_name],
                                                   self.db[self.table_name][field_name].label))    
                break ## even if one row is returned anyway
            return dto_copy
        else:
            return None
    
    def findByExample(self, dto):
        """
        @param dto:
        @return: list of DTO
        """
        if self.dto.isSimilarTo(dto):
            dto_list = []
            self_dto = self.dto.copy()
            self.dto = dto
            rows = self.getRows(Operator.EQUALS)
            if rows:
                for row in rows:
                    dto_list.append(self.findById(row.id))
            self.dto = self_dto
            return dto_list
        else:
            raise Exception("Incompatible dto object") 


    def findByExampleRecursive(self, dto):
        """
        @param dto:
        @return: list of DTO
        """
        if self.dto.isSimilarTo(dto):
            self_dto = self.dto.copy()
            self.dto = dto
            dto_list = []
            rows = self.getRows(Operator.EQUALS)
            if rows:
                for row in rows:
                    dto_list.append(self.findByIdRecursive(row.id))
            self.dto = self_dto
            return dto_list

        else:
            raise Exception("Incompatible dto object") 


    def findLike(self, dto):
        """
        SELECT LIKE EQUIVALENT
        @return: list of DTO
        """
        if self.dto.isSimilarTo(dto):
            self_dto = self.dto.copy()
            self.dto = dto
            dto_list = []
            rows = self.getRows(Operator.CONTAINS)
            if rows:
                for row in rows:
                    dto_list.append(self.findById(row.id))
            self.dto = self_dto
            return dto_list
        else:
            raise Exception("Incompatible dto object") 


    def findLikeRecursive(self, dto):
        """
        @param dto:
        @return: list of DTO
        """
        if self.dto.isSimilarTo(dto):
            self_dto = self.dto.copy()
            self.dto = dto
            dto_list = []
            rows = self.getRows(Operator.CONTAINS)
            if rows:
                for row in rows:
                    dto_list.append(self.findByIdRecursive(row.id))
            self.dto = self_dto
            return dto_list
        else:
            raise Exception("Incompatible dto object") 

    def findByComparison(self, op, dto):
        """
        @dto: example dto
        @op: Operator
        @return: list of dto
        """
        if self.dto.isSimilarTo(dto):
            dto_list = []
            self_dto = self.dto.copy()
            self.dto = dto
            rows = self.getRows(op)
            if rows:
                for row in rows:
                    dto_list.append(self.findById(row.id))
            self.dto = self_dto
            return dto_list
        else:
            raise Exception("Incompatible dto object")


    def findByComparisonRecursive(self, op, dto):
        """
        @param op: Operator
        @param dto:
        @return: list of DTO
        """
        if self.dto.isSimilarTo(dto):
            dto_list = []
            self_dto = self.dto.copy()
            self.dto = dto
            rows = self.getRows(op)
            if rows:
                for row in rows:
                    dto_list.append(self.findByIdRecursive(row.id))
            self.dto = self_dto
            return dto_list
        else:
            raise Exception("Incompatible dto object") 

    def findAll(self):
        """
        @return: list of all dto
        """
        dto_list = []
        rows = self.db(self.db[self.table_name]['id'] > 0).select()
        if rows:
            for row in rows:
                dto_list.append(self.findById(row.id))
        return dto_list

    def findAllRecursive(self):
        dto_list = []
        rows = self.db(self.db[self.table_name]['id'] > 0).select()
        if rows:
            for row in rows:
                dto_list.append(self.findByIdRecursive(row.id))
        return dto_list
        
    def query(self, query):
        """
        @param query: a DAL query
        """
        dto_list = []
        rows = self.db(query).select()
        if rows:
            for row in rows:
                dto_list.append(self.findById(row.id))
        return dto_list
    
    def __isForeignKey(self, field_name):
        """
        Verify if a field name is a foreign key
        @param field_name: field name
        @return: True if the field is foreign key 
        """
        is_foreign_key = False
        ## if IS_IN_DB is the only validator
        if "<class 'gluon.validators.IS_IN_DB'>" == str(type(self.db[self.table_name][field_name].requires)):
            is_foreign_key = True
        ## if multiples validators 
        elif type([]) == type(self.db[self.table_name][field_name].requires):
            for validator in self.db[self.table_name][field_name].requires:
                if "<class 'gluon.validators.IS_IN_DB'>" == str(type(validator)):
                    is_foreign_key = True
                    break
        return is_foreign_key
    
    def __returnParentTable(self, field_name):
        """Get the table name referenced by field_name foreign key"""
        parent_table = ''
        if type([]) == type(self.db[self.table_name][field_name].requires):
            for validator in self.db[self.table_name][field_name].requires:
                if "<class 'gluon.validators.IS_IN_DB'>" == str(type(validator)):
                    parent_table = validator.ktable
                    break
        else:
            parent_table = self.db[self.table_name][field_name].requires.ktable
        return parent_table
    
    def __returnPrimaryKey(self, field_name):
        """Get the primary key referenced by field_name"""
        if self.__isForeignKey(field_name):
            if type([]) == type(self.db[self.table_name][field_name].requires):
                for validator in self.db[self.table_name][field_name].requires:
                    if "<class 'gluon.validators.IS_IN_DB'>" == str(type(validator)):
                        return validator.kfield
            else:
                return self.db[self.table_name][field_name].requires.kfield

    
    def __returnListOfReferencedFields(self, field_name, show_id = True):
        """
        Get the list of all field names referenced by field_name
        @param field_name: the foreign key that make reference to the field in the parent table
        @param show_id: if exists, add the referenced 'id' field name in the list.
        @return: list of referenced field names in the parent table    
        """
        if show_id == True:
            show_id = True
            
        list_of_fields = []
        if type([]) == type(self.db[self.table_name][field_name].requires):
            for validator in self.db[self.table_name][field_name].requires:
                if "<class 'gluon.validators.IS_IN_DB'>" == str(type(validator)):
                    list_of_fields = validator.ks
                    break
        else:
            list_of_fields = self.db[self.table_name][field_name].requires.ks
            
        if not show_id and 'id' in list_of_fields:
            list_of_fields.remove('id')
        return list_of_fields

    def __build_query(self, field, op, value):
        """
        @note: inspired by mr.freeze
        @copyright: http://www.web2pyslices.com/main/slices/take_slice/78
        
        @param field: table field
        @param op: Operator
        @param value: value  
        """
        if op == Operator.EQUALS:
            return field == value
        elif op == Operator.NOT_EQUAL:
            return field != value
        elif op == Operator.GREATER_THAN:
            return field > value
        elif op == Operator.LESS_THAN:
            return field < value
        elif op == Operator.ENDS_WITH:
            return field.like(value+'%')
        elif op == Operator.STARTS_WITH:
            return field.like('%'+value)
        elif op == Operator.CONTAINS:
            return field.like('%'+value+'%')


